Restricting CmdExec Rights to Sysadmin

Issue

To secure your database, you should only allow members of the Sysadmin role to execute CmdExec and ActiveScripting job steps. CmdExec job steps are operating system commands or executable programs that end with .bat, .cmd, .com, or .exe. ActiveScripting job steps are scripts that are written in a scripting language, such as VBScript or PerlScript.

If you allow users who are not system administrators to run job steps, the specified proxy account is used.

Solution

Change your Microsoft® SQL Server™ Agent properties to specify that only members of the Sysadmin role can execute CmdExec or Microsoft ActiveX® scripting job steps. After you change this setting, if a user who is not a member of the Sysadmin role attempts to execute a task that includes these types of job steps, the CmdExec or ActiveScripting job steps will fail.

Instructions

To restrict CmdExec rights to Sysadmin

1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
2. Double-click Microsoft SQL Servers and SQL Server Group, and then double-click the SQL Server that you want to secure.
3. Click the Management folder, right-click SQL Server Agent, and then click Properties.
4. In the SQL Server Agent Properties dialog box, click the Job System tab.
5. Select the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box.

For MSDE, the following steps can be performed:

1. Open a command prompt window.
2. Use the osql utility to connect to the SQL instance using a valid login that is a sysadmin.
3. Issue the following command to stop non-sysadmins from running cmdexec jobs and xp_cmdshell:

   a. exec msdb.dbo.sp_set_sqlagent_properties @sysadmin_only = 1
   b. go
    

4. Issue the following command to remove the proxy account that is set:

   a. exec master.dbo.xp_sqlagent_proxy_account N'DEL'
   b. go

Additional Information

SQL Server Agent Properties

©2002-2004 Microsoft Corporation. All rights reserved.